package com.trw.trp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.trw.trp.bean.ModelBean;
import com.trw.trp.bean.ModelDetail;
import com.trw.trp.bean.TemplateBean;
import com.utuan.log.Log;
import com.utuan.log.LogFactory;

public class ModelDao extends AbstractDao {

	private static final String SQL_GET_MODELS = "select sm.model_id,m.model_name,m.parent_id from trp.workflows wf,trp.wf_status s,trp.wf_status_models sm,trp.models m where wf.workflow_id=? and wf.workflow_id=s.workflow_id and s.status_id=? and wf.workflow_id=sm.workflow_id and s.status_id=sm.status_id and sm.model_id=m.model_id order by m.parent_id,m.model_id";

	private static final String SQL_GET_MODEL_DETAIL = "select temp.field_id,temp.field_name,temp.field_type,temp.is_required,temp.related_value,temp.default_value,temp.section_id,temp.section_name,temp.section_cols,temp.section_min_width,temp.cols,temp.rows,temp.width,temp.height,temp.label_width,li.item_name from (SELECT m.field_id,m.field_name,m.field_type,m.is_required,m.list_id,m.related_value,m.default_value,ms.section_id,ms.section_name,ms.cols as section_cols,ms.min_width as section_min_width,msf.cols,msf.rows,msf.width,msf.height,msf.label_width FROM trp.model_fields m,trp.model_section ms,trp.model_section_field msf where m.model_id=? and m.model_id=ms.model_id and ms.section_id=msf.section_id and m.field_id=msf.field_id order by ms.section_order,msf.field_order) temp left outer join trp.list_items li on temp.list_id=li.list_id";
	
	private static final String SQL_GET_TEMPLATE = "select fvt.field_id,fvt.value from trp.field_value_template fvt where model_id=? and cat1=? and cat2=?";

	private static Log log = LogFactory.getLog(ModelDao.class);

	ModelDao() {
	}

	public List<ModelBean> getModels(int status) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<ModelBean> ret = null;
		Map<Long, ModelBean> modelMap = new HashMap<Long, ModelBean>();
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_MODELS);
			ps.setLong(1, 1);
			ps.setInt(2, status);
			rs = ps.executeQuery();
			while (rs.next()) {
				ModelBean model = new ModelBean();
				long modelId = rs.getLong(1);
				String modelName = rs.getString(2);
				long parentId = rs.getLong(3);
				model.setModelId(modelId);
				model.setModelName(modelName);
				if (modelMap.get(parentId) != null) {
					ModelBean parentBean = modelMap.get(parentId);
					List<ModelBean> subModelList = parentBean.getSubModelList();
					if (subModelList == null) {
						subModelList = new ArrayList<ModelBean>();
						parentBean.setSubModelList(subModelList);
					}
					subModelList.add(model);
				} else {
					modelMap.put(modelId, model);
				}
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}
		if (!modelMap.isEmpty()) {
			ret = new ArrayList<ModelBean>(modelMap.values());
			Collections.sort(ret, new Comparator<ModelBean>() {
				public int compare(ModelBean obj1, ModelBean obj2) {
					Long modelId1 = obj1.getModelId();
					Long modelId2 = obj2.getModelId();
					return modelId1.compareTo(modelId2);
				}
			});
		}
		return ret;
	}

	public ModelBean getModelById(long modelId) {
		throw new RuntimeException("Not supported method");
	}

	public ModelBean getModelByName(String modelName) {
		throw new RuntimeException("Not supported method");
	}
	
	public void removeDetail(long modelId){
		throw new RuntimeException("Not supported method");
	}

	public ModelDetail getModelDetail(long modelId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ModelDetail detail = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_MODEL_DETAIL);
			ps.setLong(1, modelId);
			rs = ps.executeQuery();
			long orgSectionId = -1;
			long orgFieldId = -1;
			long fieldId;
			String fieldName;
			int fieldType;
			boolean isRequired;
			String relatedValue;
			String fieldValue;
			String defaultValue;
			ModelDetail.ModelSection section = null;
			ModelDetail.Field field = null;
			int cols;
			int rows;
			long sectionId;
			String sectionName;
			int sectionCols;
			int sectionMinWidth;
			int fieldWidth;
			int fieldHeight;
			int labelWidth;
			while (rs.next()) {
				if (detail == null) {
					detail = new ModelDetail();
					detail.setModelId(modelId);
					detail.setSectionList(new ArrayList<ModelDetail.ModelSection>());
				}
				int i = 1;
				fieldId = rs.getLong(i++);
				fieldName = rs.getString(i++);
				fieldType = rs.getInt(i++);
				isRequired = "Y".equalsIgnoreCase(rs.getString(i++));
				relatedValue = rs.getString(i++);
				defaultValue = rs.getString(i++);
				sectionId = rs.getLong(i++);
				sectionName = rs.getString(i++);
				sectionCols = rs.getInt(i++);
				sectionMinWidth = rs.getInt(i++);
				cols = rs.getInt(i++);
				rows = rs.getInt(i++);
				fieldWidth = rs.getInt(i++);
				fieldHeight = rs.getInt(i++);
				labelWidth = rs.getInt(i++);
				fieldValue = rs.getString(i++);
				if (sectionId != orgSectionId) {
					section = new ModelDetail.ModelSection();
					section.setId(sectionId);
					section.setName(sectionName);
					section.setCols(sectionCols);
					section.setMinWidth(sectionMinWidth);
					section.setFieldList(new ArrayList<ModelDetail.Field>());
					detail.getSectionList().add(section);
					orgSectionId = sectionId;
				}
				if (fieldId != orgFieldId) {
					field = new ModelDetail.Field();
					field.setFieldId(fieldId);
					field.setFieldName(fieldName);
					field.setFieldType(fieldType);
					field.setRequired(isRequired);
					field.setRelatedValue(relatedValue);
					field.setDefaultValue(defaultValue);
					field.setCols(cols);
					field.setRows(rows);
					field.setWidth(fieldWidth);
					field.setHeight(fieldHeight);
					field.setLabelWidth(labelWidth);
					field.setValues(new ArrayList<String>());
					section.getFieldList().add(field);
					orgFieldId = fieldId;
				}
				if (!StringUtils.isEmpty(fieldValue)) {
					field.getValues().add(fieldValue);
				}
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}
		return detail;
	}
	
	public List<TemplateBean> getTemplate(long modelId,String cate1,String cate2){
		List<TemplateBean> templateList = new ArrayList<TemplateBean>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_TEMPLATE);
			ps.setLong(1, modelId);
			ps.setString(2, cate1);
			ps.setString(3, cate2);
			rs = ps.executeQuery();
			long fieldId;
			String value;
			TemplateBean bean;
			while(rs.next()){
				fieldId = rs.getLong(1);
				value = rs.getString(2);
				bean = new TemplateBean();
				bean.setFieldId(fieldId);
				bean.setValue(value);
				templateList.add(bean);
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}	
		return templateList;
	}

}
